Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Recovering from a Dropped Table: Scenario

One not-uncommon error is the accidental dropping of a table from your database. In general, the fastest and simplest solution is to use the flashback drop feature, described inOracle Database Backup and Recovery Basics, to reverse the dropping of the table. However, if for some reason, such as flashback drop being disabled or the table having been dropped with the PURGE option, you cannot use flashback table, you can create a copy of the database, perform point-in-time recovery of that copy to a time before the table was dropped, export the dropped table using an Oracle export utility, and re-import it into your primary database using an Oralce import utility.

In this scenario, assume that you do not have the flashback database functionality enabled, so `FLASHBACK` `DATABASE` is not an option, but you do have physical backups of the database.

Note: Grant powerful privileges (such as `DROP` `ANY` `TABLE`) only to only selected, appropriate users, to minimize user errors that require database recovery.

To recover a table that has been accidentally dropped, use the following procedure:

1. If possible, keep the database that experienced the user error online and available for use. Back up all datafiles of the existing database in case an error is made during the remaining steps of this procedure.

2. Restore a database backup to an alternnate location, then perform incomplete recovery of this backup using a restored backup control file, to the point just before the table was dropped.

3. Export the lost data from the temporary, restored version of the database using an Oracle export utility. In this case, export the accidentally dropped table.

   Note: System audit options are exported.

4. Use an Oracle import utility to import the data back into the production database.

5. Delete the files of the temporary copy of the database to conserve space.

See Also: Oracle Database Utilities for more information about the Oracle export and import utilities